Appending Queries and Merging Datasets in Excel

Course: Advanced Excel

Topic: Appending Queries and Merging Datasets

Course Link: Advanced Excel Course

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to Appending Queries and Merging Datasets

In Advanced Excel, data often comes from multiple worksheets, workbooks, departments, databases, or external systems. Before analyzing this information, users may need to combine related datasets into a single table or bring additional columns from one dataset into another. Power Query provides two important operations for these tasks: Append Queries and Merge Queries.

Appending combines rows from two or more tables into one table. Merging combines columns from different tables by matching records using one or more common fields. Both operations help automate data preparation, reduce manual copy-and-paste work, and create reusable data-processing workflows.

For example, a business may receive separate sales reports for January, February, and March. Appending can combine these reports into one sales table. If the sales table contains a Product ID but not the product category, merging can match Product IDs against a product master table to add category information.

2. Learning Objectives

• Understand the purpose of appending queries and merging datasets.
• Differentiate between append and merge operations.
• Prepare source tables for reliable data combination.
• Append two or more queries using Power Query.
• Merge queries using common matching columns.
• Understand join types and their effects on results.
• Handle duplicate keys, missing matches, and mismatched data types.
• Expand merged columns and create a consolidated reporting table.
• Refresh and maintain combined datasets.
• Apply data-quality checks and troubleshooting techniques.

3. What Is Power Query?

Power Query is Excel's data connection and transformation tool. It allows users to import information from supported sources, clean it, reshape it, and combine it through recorded transformation steps.

Instead of manually combining data each time a new report arrives, you can create queries that repeat the same process whenever the source data is refreshed.

Key Features of Power Query

• Connects to Excel workbooks, CSV files, folders, databases, and other supported sources.
• Removes unnecessary rows and columns.
• Changes data types and column names.
• Filters, sorts, and transforms records.
• Appends rows from multiple queries.
• Merges tables using matching columns.
• Records transformation steps for repeatable data preparation.
• Loads results to a worksheet or the Data Model, depending on the Excel version and configuration.

For additional information, visit Microsoft Power Query for Excel Help.

4. Understanding Append Queries

Appending means placing the rows from one table underneath the rows of another table. It is similar to stacking tables vertically. Append is most useful when datasets represent the same type of information for different periods, regions, branches, or business units.

Example Scenario

A retail business maintains separate monthly sales tables. Each table contains Order ID, Order Date, Region, Product, and Sales Amount. The business wants a single table containing all sales transactions.

After appending, the combined table contains four records with the same columns. The rows from February are added below the January rows.

Important Characteristics of Append

• Append combines rows rather than matching records.
• Column names are used to align fields between tables.
• Columns that exist in one query but not another may contain null values for the query that lacks those columns.
• Matching column names and compatible data types make the result easier to interpret.
• Append does not automatically remove duplicate records.

5. Types of Append Operations

Power Query provides options for appending two queries or appending three or more queries.

5.1 Append Queries

This operation combines the current query with another selected query. It is suitable when you need to combine two datasets.

5.2 Append Queries as New

This creates a separate query containing the combined result, leaving the original queries available for reuse. It is often preferable when the original queries are used elsewhere or should remain unchanged.

5.3 Append Three or More Queries

When several datasets have a similar structure, Power Query can append multiple queries in one operation. This is useful for combining sales from multiple months, branches, or regions.

6. Preparing Data Before Appending

Before appending queries, check that the datasets represent compatible information. Inconsistent column names, different data types, and mixed reporting structures can create confusing results.

Preparation Checklist

• Confirm that all tables contain the same type of records.
• Use consistent column names across the source queries.
• Check that date columns use date data types.
• Ensure numeric columns use compatible numeric data types.
• Remove unnecessary title rows and blank rows.
• Confirm that identifiers are represented consistently.
• Investigate duplicate records before combining the tables.
• Add a source or period column if you need to identify where each row originated.

Example of Inconsistent Column Names

7. Step-by-Step: Appending Two Queries

1. Open the Excel workbook containing the queries, or create queries from the required source tables.
2. Select the Data tab.
3. Open the Power Query Editor using Get Data or Queries & Connections, as appropriate.
4. In Power Query Editor, select the Home tab.
5. Choose Append Queries or Append Queries as New.
6. Select the option for two tables or queries.
7. Choose the first query and the second query.
8. Confirm the selection.
9. Inspect the appended result and verify the column names and row count.
10. Check for null values, duplicate records, and data-type inconsistencies.
11. Select Close & Load or the appropriate load option.

Tip: Use Append Queries as New when you want to preserve the source queries and create a separate combined result for reporting.

8. Step-by-Step: Appending Three or More Queries

Suppose a company has separate queries named JanuarySales, FebruarySales, MarchSales, and AprilSales. All four queries contain compatible sales records.

1. Open Power Query Editor.
2. Select Home > Append Queries > Append Queries as New.
3. Choose the option for three or more tables or queries.
4. Select each query that needs to be included.
5. Add the selected queries to the list of tables to append.
6. Confirm the operation.
7. Review the combined records and ensure all expected periods are represented.
8. Rename the new query to qry_AllSales.
9. Load the result into the required destination.

Use clear query names so that future users can understand the purpose of the combined dataset.

9. Example: Appending Monthly Sales Reports

Consider these two monthly sales datasets.

January Data

February Data

Expected Appended Result

The combined table contains four rows. You can use this table to build a PivotTable showing sales by month, product, or another business category.

10. Understanding Merge Queries

Merging combines two tables by matching records using one or more common columns. It is similar to joining tables in a relational database. A merge can bring additional information from a lookup or reference table into a main dataset.

Example Scenario

A sales table contains Order ID, Product ID, and Sales Amount. A separate product master table contains Product ID, Product Name, and Category. By merging on Product ID, you can add the product name and category to each sales record.

Sales Table

Product Master Table

Merging these tables on Product ID allows the sales table to include Product Name and Category.

11. Append vs. Merge

12. Understanding Join Types in Merge Queries

When merging queries, you choose a join type that determines which records from the two datasets appear in the result. The correct choice depends on the reporting requirement.

12.1 Left Outer Join

Returns all rows from the first table and matching rows from the second table. If no match exists in the second table, the expanded fields from that table are null.

Example: Keep every sales transaction and add product information where a matching Product ID exists.

12.2 Right Outer Join

Returns all rows from the second table and matching rows from the first table. Rows without a match in the first table remain, with null values for fields from the first table.

Example: Keep every product in the product master, including products that have no matching sales records.

12.3 Full Outer Join

Returns all rows from both tables. Matching records are combined, while unmatched records from either table are retained with null values for fields that have no match.

Example: Compare two inventory systems to identify records that appear in only one system as well as records that match.

12.4 Inner Join

Returns only records that have matching values in both tables.

Example: Create a dataset containing only sales transactions with a matching product master record.

12.5 Left Anti Join

Returns rows from the first table that do not have matching records in the second table.

Example: Identify sales transactions whose Product IDs are missing from the product master.

12.6 Right Anti Join

Returns rows from the second table that do not have matching records in the first table.

Example: Identify products in the product master that have no matching records in a sales table.

Join Type Summary

13. Step-by-Step: Merging Two Queries

1. Open Excel and load the datasets into Power Query as separate queries.
2. Open Power Query Editor.
3. Select the main query that should contain the combined result.
4. Choose Home > Merge Queries or Merge Queries as New.
5. Select the second query from the available list.
6. Select the matching column in the first table.
7. Select the corresponding matching column in the second table.
8. For multiple-column matching, select the required columns in the same order in both tables, using the supported selection method.
9. Choose the appropriate join type.
10. Review the match information shown in the merge dialog.
11. Select OK.
12. Inspect the newly added nested-table column.
13. Use the expand control in the column header to select the fields to bring into the main table.
14. Rename the expanded columns if necessary.
15. Validate the results and load the query.

Important: The matching columns should have compatible data types and consistent values. A merge can return few or no matches if keys contain extra spaces, different formats, or different representations of the same value.

14. Expanding Merged Columns

After a merge, Power Query usually adds a column containing nested tables from the second query. You must expand this column to display the fields you want to include in the final dataset.

Expansion Procedure

1. Locate the new merged column in Power Query Editor.
2. Select the expand icon in the column header.
3. Choose the required fields, such as Product Name and Category.
4. Decide whether to retain the source column name as a prefix, if the option is available.
5. Confirm the selection.
6. Review the expanded values and check for nulls or unexpected duplicates.

Only expand fields that are necessary for the analysis. Keeping unnecessary columns can make the dataset harder to maintain and may reduce performance.

15. Example: Merging Sales with Product Details

Suppose the sales query contains three transactions, while the product master contains information for three products.

Expected Result After a Left Outer Merge

The merged dataset can now support analysis of sales by product category, product name, or transaction. This is an example of enriching a transaction table with descriptive information from a reference table.

16. Merging on Multiple Columns

Sometimes one column is not enough to identify a unique record. In such cases, a merge can use multiple matching columns.

Example

A company has monthly targets for each region. A target table may contain Region and Month, while a sales table contains the same fields. If the same region appears in multiple months, matching only on Region could associate a sales record with the wrong target period.

Instead, match using both Region and Month. Ensure that both columns have compatible types and consistent values in the two tables.

Important Considerations

• Select matching columns in the same order in both tables.
• Ensure that each field represents the same business concept.
• Standardize date formats and text values before merging.
• Investigate whether the combined key is unique in the reference table.
• Check unmatched records after the merge.

17. Understanding Duplicate Keys and Many-to-Many Matches

A matching key is a column or combination of columns used to identify related records. Ideally, a lookup or reference table contains one record per key when the goal is to add descriptive information to each transaction.

If the second table contains multiple records with the same key, one row in the first table may match several rows in the second table. Expanding the merge can therefore increase the number of output rows.

Example

If a sales transaction for P01 matches both rows, the merged output may contain two rows for that transaction. This can inflate totals in later PivotTables or calculations.

How to Prevent Unexpected Duplicates

• Check whether the matching key should be unique.
• Use Power Query's duplicate detection or grouping tools to inspect repeated keys.
• Remove duplicates only when doing so is consistent with the business rules.
• Use additional matching columns when they correctly identify a record.
• Do not delete legitimate repeated transactions merely to force a unique key.
• Compare the row count before and after expanding the merge.

18. Handling Unmatched Records and Null Values

Null values in expanded columns often indicate that no matching record was found. This may be expected or may reveal a data-quality problem.

Common Reasons for Unmatched Records

• The key does not exist in the second table.
• One table stores the key as text while the other uses a numeric type.
• Text contains leading or trailing spaces.
• Values use different formats, such as P-001 versus P001.
• One source contains outdated or incomplete reference data.
• The wrong matching column was selected.

Recommended Checks

1. Compare the source values in both tables.
2. Confirm the data types.
3. Trim unnecessary spaces where appropriate.
4. Standardize casing and formats if the business rules permit it.
5. Use an anti join to identify records without matches.
6. Resolve genuine data-quality problems with the data owner.

Do not automatically replace every null with zero or a generic label. A missing match may mean that a record is invalid, that reference information is incomplete, or that the relationship is not applicable.

19. Append and Merge in Real Business Scenarios

20. Combining Append and Merge in One Workflow

Many business tasks require both operations. A typical process first appends transaction datasets from different periods, then merges the consolidated table with reference information.

Example Workflow

1. Import monthly sales files into separate Power Query queries.
2. Standardize column names and data types.
3. Append the monthly sales queries into one transaction query.
4. Import a product master table.
5. Merge the combined sales query with the product master using Product ID.
6. Expand Product Name and Category.
7. Review unmatched Product IDs and duplicate lookup keys.
8. Load the final dataset.
9. Create PivotTables and charts for management reporting.

This workflow combines records from multiple sources and then enriches those records with descriptive attributes. It is a common pattern in business reporting and data preparation.

21. Power Query M Language Basics

Power Query records transformation steps using the M language. Most users can append and merge through the interface without writing code, but understanding the basic functions can help when inspecting or customizing queries.

21.1 Table.Combine for Appending

The Table.Combine function combines a list of tables into one table.

= Table.Combine({JanuarySales, FebruarySales})

In this example, JanuarySales and FebruarySales represent existing query or table expressions. The resulting table contains the rows from both sources, with columns aligned by name.

21.2 Table.NestedJoin for Merging

The Table.NestedJoin function joins tables using matching key columns and stores matching rows from the second table in a nested-table column.

= Table.NestedJoin(
    Sales,
    {"Product ID"},
    Products,
    {"Product ID"},
    "ProductDetails",
    JoinKind.LeftOuter
)

This example merges Sales with Products using Product ID and keeps every row from Sales. Matching rows from Products are stored in the ProductDetails column until expanded.

21.3 Table.ExpandTableColumn for Expanding

The Table.ExpandTableColumn function expands selected fields from a nested-table column.

= Table.ExpandTableColumn(
    MergedQuery,
    "ProductDetails",
    {"Product Name", "Category"},
    {"Product Name", "Category"}
)

This example expands Product Name and Category from ProductDetails into separate columns.

Important M Language Notes

• Query names and column names must match the actual names in your workbook.
• Spaces and special characters in identifiers may require appropriate M syntax.
• The join type determines which records are retained.
• Always validate the result after changing a query expression.
• Use the Power Query interface to generate the basic steps if you are unfamiliar with M syntax.

22. Common Problems and Troubleshooting

23. Best Practices for Reliable Data Combination

• Choose Append for stacking compatible records and Merge for matching related records.
• Keep source queries unchanged when you need to reuse them; create a new query for the combined output when appropriate.
• Standardize column names before appending.
• Confirm matching key types and formats before merging.
• Use meaningful query and column names.
• Check duplicate keys in lookup tables.
• Validate row counts before and after each operation.
• Investigate unmatched records instead of ignoring them.
• Do not remove duplicates unless the business rules justify doing so.
• Keep only the fields needed for the final report.
• Document the purpose of each transformation.
• Refresh the query and test the output when source data changes.
• Compare totals against trusted source reports.
• Protect confidential data and use authorized source access.

24. Hands-On Exercise: Append Monthly Sales

Objective: Combine sales data from two periods into one table.

January Data

February Data

Tasks

1. Convert both datasets into Excel Tables.
2. Import both tables into Power Query.
3. Confirm that the column names and types are consistent.
4. Append the two queries as a new query.
5. Name the result qry_CombinedSales.
6. Load the combined table to a worksheet.
7. Check that four records are present.
8. Create a PivotTable showing sales by month.

25. Hands-On Exercise: Merge Sales with Product Details

Objective: Add product descriptions to the combined sales table.

Product Master

Tasks

1. Import the product master table into Power Query.
2. Open the combined sales query.
3. Merge it with the product master using Product ID.
4. Select a Left Outer join.
5. Expand Product Name and Category.
6. Check that each sales transaction has the expected product details.
7. Investigate any null product names or unexpected extra rows.
8. Create a PivotTable summarizing sales by product category.

26. Mini Project: Consolidated Sales Reporting System

Build a repeatable reporting workflow that combines multiple monthly sales files and adds product information from a product master dataset.

Project Requirements

• Import at least three monthly sales datasets.
• Standardize the columns and data types.
• Append the monthly queries into a consolidated transaction table.
• Import a separate product master query.
• Merge product information into the consolidated sales table.
• Check for duplicate keys and unmatched products.
• Load the final result into Excel.
• Create a PivotTable for sales by month and category.
• Build a chart that summarizes sales performance.
• Test whether the report can be refreshed after a source file changes.

Project Evaluation Checklist

• Are all expected source files included?
• Are the appended columns aligned correctly?
• Are matching keys consistent and correctly typed?
• Does the merge produce the expected row count?
• Are duplicate and unmatched records investigated?
• Do summary totals reconcile with the source files?
• Can another user understand the query names and steps?

27. Interview Questions and Answers

Q1. What is Append Queries in Excel?

Append Queries combines rows from two or more queries into one table. It is useful for combining datasets that represent the same type of information.

Q2. What is Merge Queries?

Merge Queries combines related tables by matching one or more columns. It can add descriptive fields from a reference table to a main dataset.

Q3. What is the main difference between append and merge?

Append stacks rows vertically, while merge matches records and combines information from multiple tables, usually by adding columns.

Q4. What is a Left Outer join?

A Left Outer join keeps all rows from the first table and brings in matching records from the second table. Unmatched rows from the first table remain with null values in the expanded fields.

Q5. What is an Inner join?

An Inner join retains only records with matching key values in both tables.

Q6. What is a Full Outer join?

A Full Outer join retains all records from both tables, combining matching records and preserving unmatched records with nulls where necessary.

Q7. Why can a merge increase the number of rows?

If a matching key appears multiple times in the second table, a single row from the first table can match several records. Expanding those matches can increase the output row count.

Q8. What is the purpose of expanding a merged column?

Expansion selects fields from the nested table created by the merge and displays them as regular columns in the resulting dataset.

Q9. How can unmatched records be identified?

Use a suitable anti join or inspect null values in the expanded fields, then investigate the matching keys and source data.

Q10. Can Append Queries remove duplicate rows automatically?

No. Appending combines rows but does not automatically remove duplicates. Duplicate handling must be performed separately when appropriate.

Q11. Why should data types match before merging?

Inconsistent data types can prevent otherwise equivalent values from matching and may produce unexpected results.

Q12. What is Table.Combine?

Table.Combine is an M language function that combines a list of tables into one table, commonly used for appending data.

Q13. What is Table.NestedJoin?

Table.NestedJoin creates a join between tables and stores the matching rows from the second table in a nested-table column.

Q14. When should Append Queries as New be used?

Use it when you want to create a separate combined query while keeping the original source queries available for reuse.

Q15. Why is validating row counts important?

Row-count checks help identify missing records, unexpected duplicates, and changes caused by the selected join type or source data.

28. Quick Revision Checklist

• Append combines rows; Merge combines related information through matching keys.
• Use compatible column names and data types before appending.
• Choose an appropriate join type when merging.
• Expand merged columns to select the fields needed in the final result.
• Check the uniqueness of lookup keys.
• Investigate nulls and unmatched records.
• Validate row counts and summary totals.
• Use meaningful query names and preserve source queries where useful.
• Refresh and test the final output when source data changes.
• Combine append and merge operations when a reporting workflow requires both.

29. Conclusion

Appending queries and merging datasets are essential techniques for preparing data in Advanced Excel. Append is used to combine rows from compatible datasets, while Merge connects related tables through matching fields. Understanding the difference between these operations helps users select the right method for consolidating sales, inventory, employee, customer, and financial data.

By using Power Query, applying appropriate join types, standardizing key columns, checking duplicates, and validating the final results, you can build reliable data preparation workflows that are easier to refresh and maintain. These skills support accurate PivotTables, dashboards, and business reports.

30. Additional Learning Resources

• Explore the Advanced Excel Course
• Register for a Course Demo
• Visit the JustAcademy Website
• Microsoft Support: Power Query for Excel
• Microsoft Support: Power Query Data Sources